package com.servicesDirectory.dao.organisation.directorate;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.servicesDirectory.common.exception.FunctionalException;
import com.servicesDirectory.common.exception.SystemException;
import com.servicesDirectory.common.vo.BaseProgrammeVO;
import com.servicesDirectory.common.vo.organisation.DirectorateVO;
import com.servicesDirectory.dao.BaseDAOImpl;

public class DirectorateDAOImpl implements DirectorateDAO {
	DirectorateVO dir;
	ArrayList<DirectorateVO> listDirectorate = new ArrayList<DirectorateVO>();
	BaseDAOImpl base = new BaseDAOImpl();
	private boolean isSuccess = false;
	public Connection con = null;
	private int result;

	@Override
	public boolean insert(DirectorateVO dir) throws FunctionalException,
			SystemException {

		// TODO Auto-generated method stub
		isSuccess = false;
		// TODO Auto-generated method stub
		try {
			con = getConnection();
			Statement st = con.createStatement();

			String sql = "INSERT INTO Directorate VALUES('"
					+ dir.getDirectorateName() + "','" + dir.getShortDes()
					+ "'," + dir.getContactID() + ",'" + dir.getCopyAddr()
					+ "','" + dir.getAddr1() + "','" + dir.getAddr2() + "','"
					+ dir.getAddr3() + "','" + dir.getPostcode() + "','"
					+ dir.getTownName() + "','" + dir.getCountyName() + "','"
					+ dir.getCountryName() + "'," + dir.getTypeOfBusinessID()
					+ ",'" + dir.getFullDesc() + "','" + dir.getPhoneNumber()
					+ "','" + dir.getFax() + "','" + dir.getEmail() + "','"
					+ dir.getWebAddr() + "','" + dir.getCharityNumber() + "','"
					+ dir.getCompanyNumber() + "','" + dir.getOrganisationID()
					+ "'," + dir.getIsActive() + ")";
			result = st.executeUpdate(sql);
			if (result > 0) {
				isSuccess = true;
			}
			closeConnection(con, st);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return isSuccess;
	}

	@Override
	public boolean update(DirectorateVO dir) throws FunctionalException,
			SystemException {
		isSuccess = false;
		try {
			con = getConnection();
			Statement st = con.createStatement();
			String sql = "UPDATE Directorate SET DirectorateName = '"
					+ dir.getDirectorateName() + "',ShortDesc = '"
					+ dir.getShortDes() + "',ContactID = " + dir.getContactID()
					+ ",Addr1 = '" + dir.getAddr1() + "',Addr2 = '"
					+ dir.getAddr2() + "',Addr3 = '" + dir.getAddr3()
					+ "',Postcode = '" + dir.getPostcode() + "',TownName = '"
					+ dir.getTownName() + "',CountyName = '"
					+ dir.getCountyName() + "',CountryName = '"
					+ dir.getCountryName() + "',TypeOfBusinessID = "
					+ dir.getTypeOfBusinessID() + ",FullDesc = '"
					+ dir.getFullDesc() + "',PhoneNumber = '"
					+ dir.getPhoneNumber() + "',Fax = '" + dir.getFax()
					+ "',Email = '" + dir.getEmail() + "',WebAddr = '"
					+ dir.getWebAddr() + "',CharityNumber = '"
					+ dir.getCharityNumber() + "',CompanyNumber = '"
					+ dir.getCompanyNumber() + "',IsActive = "
					+ dir.getIsActive() + ",OrganisationID = "
					+ dir.getOrganisationID() + " WHERE DirectorateID ="
					+ dir.getDirectorateID();
			result = st.executeUpdate(sql);
			if (result > 0) {
				isSuccess = true;
			} else {
				isSuccess = false;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		// TODO Auto-generated method stub
		return isSuccess;
	}

	@Override
	public Connection getConnection() {
		// TODO Auto-generated method stub
		return base.getConnection();
	}

	@Override
	public void closeConnection(Connection con, Statement st) {
		// TODO Auto-generated method stub
		base.closeConnection(con, st);
	}

	public List<DirectorateVO> search(int directorateID) {

		try {
			con = getConnection();
			Statement st = con.createStatement();
			String sql = "SELECT DirectorateID,DirectorateName, ShortDesc,Directorate.ContactID,"
					+ "CopyAddr,Addr1,Addr2,Addr3,Postcode,TownName,CountyName,CountryName,"
					+ "Directorate.TypeOfBusinessID, TypeOfBusiness.BusinessName, FullDesc,PhoneNumber,"
					+ "Fax,Directorate.Email,WebAddr,CharityNumber,CompanyNumber, OrganisationID,Directorate.IsActive, surName, firstName, SICCode "
					+ "FROM Directorate INNER JOIN TypeOfBusiness ON Directorate.TypeOfBusinessID = TypeOfBusiness.TypeOfBusinessID "
					+ "INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID "
					+ " WHERE Directorate.DirectorateID=" + directorateID;

			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				String directorateName = rs.getString(2);
				String shortDes = rs.getString(3);
				int contactID = rs.getInt(4);
				int copyAddr = rs.getInt(5);
				String addr1 = rs.getString(6);
				String addr2 = rs.getString(7);
				String addr3 = rs.getString(8);
				String postcode = rs.getString(9);
				String townName = rs.getString(10);
				String countyName = rs.getString(11);
				String countryName = rs.getString(12);
				int typeOfBusinessID = rs.getInt(13);
				String typeOfBusinessName = rs.getString(14);
				String fullDesc = rs.getString(15);
				String phoneNumber = rs.getString(16);
				String fax = rs.getString(17);
				String email = rs.getString(18);
				String webAddr = rs.getString(19);
				String charityNumber = rs.getString(20);
				String companyNumber = rs.getString(21);
				int organisationID = rs.getInt(22);
				int isActive = rs.getInt(23);
				String contactName = rs.getString(24)+" "+ rs.getString(25);
				String sicCode = rs.getString(26);
				dir = new DirectorateVO(directorateID, directorateName,
						shortDes, contactID, copyAddr, addr1, addr2, addr3,
						postcode, townName, countyName, countryName,
						typeOfBusinessID, typeOfBusinessName, fullDesc,
						phoneNumber, fax, email, webAddr, charityNumber,
						companyNumber, isActive, organisationID, contactName, sicCode);
				listDirectorate.add(dir);
			}
			return listDirectorate;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<DirectorateVO> list(String filter, boolean isInclude)
			throws FunctionalException, SystemException {
		// TODO Auto-generated method stub
		String qr = null;
		Statement st = null;
		DirectorateVO dir = null;
		String qrNotInclude = " AND Directorate.IsActive = 1";
		listDirectorate = new ArrayList<DirectorateVO>();
		try {
			con = getConnection();
			st = con.createStatement();

			if (isInclude == true) {
				if (filter.equals("ABCDE")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'A%' "
							+ "OR Directorate.DirectorateName LIKE 'B%' "
							+ "OR Directorate.DirectorateName LIKE 'C%' "
							+ "OR Directorate.DirectorateName LIKE 'D%' "
							+ "OR Directorate.DirectorateName LIKE 'E%'";
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'F%' "
							+ "OR Directorate.DirectorateName LIKE 'G%' "
							+ "OR Directorate.DirectorateName LIKE 'H%' "
							+ "OR Directorate.DirectorateName LIKE 'I%' "
							+ "OR Directorate.DirectorateName LIKE 'J%'";
				} else if (filter.equals("KLMN")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'K%' "
							+ "OR Directorate.DirectorateName LIKE 'L%' "
							+ "OR Directorate.DirectorateName LIKE 'M%' "
							+ "OR Directorate.DirectorateName LIKE 'N%' ";
				} else if (filter.equals("OPQR")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'O%' "
							+ "OR Directorate.DirectorateName LIKE 'P%' "
							+ "OR Directorate.DirectorateName LIKE 'Q%' "
							+ "OR Directorate.DirectorateName LIKE 'R%' ";
				} else if (filter.equals("STUV")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'S%' "
							+ "OR Directorate.DirectorateName LIKE 'T%' "
							+ "OR Directorate.DirectorateName LIKE 'U%' "
							+ "OR Directorate.DirectorateName LIKE 'V%' ";
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'W%' "
							+ "OR Directorate.DirectorateName LIKE 'X%' "
							+ "OR Directorate.DirectorateName LIKE 'Y%' "
							+ "OR Directorate.DirectorateName LIKE 'Z%' ";
				} else if (filter.equals("0-9")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE '0%' "
							+ "OR Directorate.DirectorateName LIKE '1%' "
							+ "OR Directorate.DirectorateName LIKE '2%' "
							+ "OR Directorate.DirectorateName LIKE '3%' "
							+ "OR Directorate.DirectorateName LIKE '4%' "
							+ "OR Directorate.DirectorateName LIKE '5%' "
							+ "OR Directorate.DirectorateName LIKE '6%' "
							+ "OR Directorate.DirectorateName LIKE '7%' "
							+ "OR Directorate.DirectorateName LIKE '8%' "
							+ "OR Directorate.DirectorateName LIKE '9%' ";

				} else {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID";
				}
			} else {
				if (filter.equals("ABCDE")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'A%' "
							+ "OR Directorate.DirectorateName LIKE 'B%' "
							+ "OR Directorate.DirectorateName LIKE 'C%' "
							+ "OR Directorate.DirectorateName LIKE 'D%' "
							+ "OR Directorate.DirectorateName LIKE 'E%' "
							+ qrNotInclude;
				} else if (filter.equals("FGHIJ")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'F%' "
							+ "OR Directorate.DirectorateName LIKE 'G%' "
							+ "OR Directorate.DirectorateName LIKE 'H%' "
							+ "OR Directorate.DirectorateName LIKE 'I%' "
							+ "OR Directorate.DirectorateName LIKE 'J%' "
							+ qrNotInclude;
				} else if (filter.equals("KLMN")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'K%' "
							+ "OR Directorate.DirectorateName LIKE 'L%' "
							+ "OR Directorate.DirectorateName LIKE 'M%' "
							+ "OR Directorate.DirectorateName LIKE 'N%' "
							+ qrNotInclude;
				} else if (filter.equals("OPQR")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'O%' "
							+ "OR Directorate.DirectorateName LIKE 'P%' "
							+ "OR Directorate.DirectorateName LIKE 'Q%' "
							+ "OR Directorate.DirectorateName LIKE 'R%' "
							+ qrNotInclude;
				} else if (filter.equals("STUV")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'S%' "
							+ "OR Directorate.DirectorateName LIKE 'T%' "
							+ "OR Directorate.DirectorateName LIKE 'U%' "
							+ "OR Directorate.DirectorateName LIKE 'V%' "
							+ qrNotInclude;
				} else if (filter.equals("WXYZ")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE 'W%' "
							+ "OR Directorate.DirectorateName LIKE 'X%' "
							+ "OR Directorate.DirectorateName LIKE 'Y%' "
							+ "OR Directorate.DirectorateName LIKE 'Z%' "
							+ qrNotInclude;
				} else if (filter.equals("0-9")) {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID WHERE "
							+ " Directorate.DirectorateName LIKE '0%' "
							+ "OR Directorate.DirectorateName LIKE '1%' "
							+ "OR Directorate.DirectorateName LIKE '2%' "
							+ "OR Directorate.DirectorateName LIKE '3%' "
							+ "OR Directorate.DirectorateName LIKE '4%' "
							+ "OR Directorate.DirectorateName LIKE '5%' "
							+ "OR Directorate.DirectorateName LIKE '6%' "
							+ "OR Directorate.DirectorateName LIKE '7%' "
							+ "OR Directorate.DirectorateName LIKE '8%' "
							+ "OR Directorate.DirectorateName LIKE '9%' "
							+ qrNotInclude;

				} else {
					qr = "SELECT Directorate.DirectorateID, Directorate.DirectorateName, Directorate.Addr1, Directorate.Postcode, Contact.surName, Contact.firstName, Directorate.IsActive "
							+ "FROM Directorate INNER JOIN Contact ON Contact.ContactID=Directorate.ContactID "
							+ qrNotInclude;
				}
			}
			ResultSet rs = st.executeQuery(qr);
			while (rs.next()) {
				int directorateID = rs.getInt(1);
				String directorateName = rs.getString(2);
				String addr1 = rs.getString(3);
				String postcode = rs.getString(4);
				String contactName = rs.getString(5) + " " + rs.getString(6);
				int intIsActive = rs.getInt(7);
				dir = new DirectorateVO(directorateID, directorateName, addr1,
						postcode, contactName, intIsActive);
				listDirectorate.add(dir);
			}
			return listDirectorate;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		base.closeConnection(con, st);
		return null;
	}

}
